﻿using PetaPoco.Core;
using PetaPoco.Internal;
using PetaPoco.Providers;
using PetaPoco.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using PmSoft;
using PmSoft.PetaPoco.Extend;

namespace PetaPoco
{
	public partial class Database : IExtend, IDbContextTransaction
#if ASYNC
        IExtendAsync
#endif
	{
		/// <summary>
		/// 执行多条Sql
		/// </summary>
		/// <param name="sqls"></param>
		/// <returns>总影响行数</returns>
		public int Execute(IEnumerable<Sql> sqls)
		{
			int count = 0;
			try
			{
				OpenSharedConnection();
				try
				{
					foreach (Sql sql in sqls)
					{
						using (IDbCommand command = this.CreateCommand(this.Connection, sql.SQL, sql.Arguments))
						{
							count += command.ExecuteNonQuery();
							OnExecutedCommand(command);
							continue;
						}
					}
				}
				finally
				{
					CloseSharedConnection();
				}
			}
			catch (Exception exception)
			{
				this.OnException(exception);
				throw;
			}
			return count;
		}

#if ASYNC
        /// <summary>
        /// 执行多条Sql
        /// </summary>
        /// <param name="sqls"></param>
        /// <returns>总影响行数</returns>
        public async Task<int> ExecuteAsync(IEnumerable<Sql> sqls)
        {
            int count = 0;
            try
            {
                await OpenSharedConnectionAsync();
                try
                {
                    foreach (Sql sql in sqls)
                    {
                        using (IDbCommand command = this.CreateCommand(this.Connection, sql.SQL, sql.Arguments))
                        {
                            count += command.ExecuteNonQuery();
                            OnExecutedCommand(command);
                            continue;
                        }
                    }
                }
                finally
                {
                    CloseSharedConnection();
                }
            }
            catch (Exception exception)
            {
                this.OnException(exception);
                throw;
            }
            return count;
        }
#endif

		/// <summary>
		/// 根据主键值查询集合
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="primaryKeys"></param>
		/// <returns></returns>
		public IEnumerable<T> FetchByPrimaryKeys<T>(IEnumerable<object> primaryKeys)
		{
			if (primaryKeys == null)
				throw new ArgumentNullException(nameof(primaryKeys));
			if (primaryKeys.Count() == 0)
				return new List<T>();
			string primaryKey = _provider.EscapeSqlIdentifier(PocoData.ForType(typeof(T), DefaultMapper).TableInfo.PrimaryKey);
			Sql sql = Sql.Builder.Where($"{primaryKey} IN(@PrimaryKeys)", new { PrimaryKeys = primaryKeys });
			return Fetch<T>(sql);
		}

#if ASYNC
        /// <summary>
        /// 根据主键值查询集合
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryKeys"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public async Task<IEnumerable<T>> FetchByPrimaryKeysAsync<T>(IEnumerable<object> primaryKeys)
        {
            if (primaryKeys == null)
                throw new ArgumentNullException(nameof(primaryKeys));
            if (primaryKeys.Count() == 0)
                return new List<T>();
            string primaryKey = _provider.EscapeSqlIdentifier(PocoData.ForType(typeof(T), DefaultMapper).TableInfo.PrimaryKey);
            Sql sql = Sql.Builder.Where($"{primaryKey} IN(@PrimaryKeys)", new { PrimaryKeys = primaryKeys });
            return await FetchAsync<T>(sql);
        }
#endif

		/// <summary>
		/// 获取第一列组成的集合
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		public IEnumerable<T> FetchFirstColumn<T>(Sql sql)
		{
			return FetchFirstColumn<T>(sql.SQL, sql.Arguments);
		}

#if ASYNC
        /// <summary>
        /// 获取第一列组成的集合
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> FetchFirstColumnAsync<T>(Sql sql)
        {
            return await FetchFirstColumnAsync<T>(sql.SQL, sql.Arguments);
        }
#endif

		/// <summary>
		/// 获取第一列组成的集合
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="sql"></param>
		/// <param name="args"></param>
		/// <returns></returns>
		public IEnumerable<T> FetchFirstColumn<T>(string sql, params object[] args)
		{
			List<T> list = new List<T>();
			try
			{
				this.OpenSharedConnection();
				using (IDbCommand command = CreateCommand(this.Connection, sql, args))
				{
					using (IDataReader reader = command.ExecuteReader())
					{
						this.OnExecutedCommand(command);
						while (reader.Read())
						{
							if (reader[0] is T)
								list.Add((T)reader[0]);
						}
						reader.Close();
					}
					//Type type = list.FirstOrDefault().GetType();
					return list;
				}
			}
			finally
			{
				this.CloseSharedConnection();
			}
		}

#if ASYNC
        /// <summary>
        /// 获取第一列组成的集合
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> FetchFirstColumnAsync<T>(string sql, params object[] args)
        {
            List<T> list = new List<T>();
            try
            {
                await OpenSharedConnectionAsync();
                using (IDbCommand command = CreateCommand(this.Connection, sql, args))
                {
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        this.OnExecutedCommand(command);
                        while (reader.Read())
                        {
                            if (reader[0] is T)
                                list.Add((T)reader[0]);
                        }
                        reader.Close();
                    }
                    //Type type = list.FirstOrDefault().GetType();
                    return list;
                }
            }
            finally
            {
                this.CloseSharedConnection();
            }
        }
#endif

		/// <summary>
		/// 构建获取前topNumber记录的SQL 
		/// </summary>
		/// <param name="topNumber"></param>
		/// <param name="sql"></param>
		/// <returns></returns>
		protected string BuildTopSql(int topNumber, string sql)
		{
			Match match = ((PagingHelper)Provider.PagingUtility).RegexColumns.Match(sql);
			if (!match.Success)
				return string.Empty;
			Group group = match.Groups[1];
			if (this._provider is MySqlDatabaseProvider || this.Provider is MySqlConnectorDatabaseProvider)
			{
				return string.Concat(new object[] { sql.Substring(0, group.Index), " ", group.Value, " ", sql.Substring(group.Index + group.Length), " limit ", topNumber });
			}
			return string.Concat(new object[] { sql.Substring(0, group.Index), " top ", topNumber, " ", group.Value, " ", sql.Substring(group.Index + group.Length) });
		}

		/// <summary>
		/// 构建获取前topNumber记录的SQL 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="topNumber"></param>
		/// <param name="sql"></param>
		/// <returns></returns>
		protected string BuildTopSql<T>(int topNumber, string sql)
		{
			PocoData data = PocoData.ForType(typeof(T), DefaultMapper);
			string primaryKey = data.TableInfo.TableName + "." + data.TableInfo.PrimaryKey;
			if (this.EnableAutoSelect)
			{
				sql = Provider.AddSelectClause<T>(sql, DefaultMapper, primaryKey);
			}
			return this.BuildTopSql(topNumber, sql);
		}

		/// <summary>
		/// 创建分页的SQL语句 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="maxRecords"></param>
		/// <param name="skip"></param>
		/// <param name="take"></param>
		/// <param name="sql"></param>
		/// <param name="args"></param>
		/// <param name="sqlCount"></param>
		/// <param name="sqlPage"></param>
		protected void BuildPagingPrimaryKeyQueries<T>(long maxRecords, long skip, long take, string sql, ref object[] args, out string sqlCount, out string sqlPage)
		{
			PocoData data = PocoData.ForType(typeof(T), DefaultMapper);
			string primaryKey = string.Empty;
			if (sql.Contains(data.TableInfo.TableName))
				primaryKey = data.TableInfo.TableName + "." + data.TableInfo.PrimaryKey;
			else
				primaryKey = data.TableInfo.PrimaryKey;
			if (this.EnableAutoSelect)
			{
				sql = Provider.AddSelectClause<T>(sql, DefaultMapper, primaryKey);
			}
			this.BuildPagingPrimaryKeyQueries(maxRecords, skip, take, primaryKey, sql, ref args, out sqlCount, out sqlPage);
		}

		/// <summary>
		/// 创建分页的SQL语句 
		/// </summary>
		/// <param name="maxRecords"></param>
		/// <param name="skip"></param>
		/// <param name="take"></param>
		/// <param name="primaryKey"></param>
		/// <param name="sql"></param>
		/// <param name="args"></param>
		/// <param name="sqlCount"></param>
		/// <param name="sqlPage"></param>
		protected void BuildPagingPrimaryKeyQueries(long maxRecords, long skip, long take, string primaryKey, string sql, ref object[] args, out string sqlCount, out string sqlPage)
		{
			if (!SplitSqlForPagingOptimized(maxRecords, sql, primaryKey, out sqlCount, out string sqlSelectRemoved, out string sqlOrderBy))
				throw new Exception("Unable to parse SQL statement for paged query");
			SQLParts parts;
			parts.Sql = sql;
			parts.SqlCount = sqlCount;
			parts.SqlSelectRemoved = sqlSelectRemoved;
			parts.SqlOrderBy = sqlOrderBy;
			sqlPage = Provider.BuildPageQuery(skip, take, parts, ref args);
		}

		/// <summary>
		/// 获取可分页的主键集合 
		/// </summary>
		/// <typeparam name="TEntity"></typeparam>
		/// <param name="maxRecords"></param>
		/// <param name="pageSize"></param>
		/// <param name="pageIndex"></param>
		/// <param name="sql"></param>
		/// <returns></returns>
		public PagingEntityIdCollection FetchPagingPrimaryKeys<TEntity>(long maxRecords, int pageSize, int pageIndex, Sql sql) where TEntity : IEntity
		{
			if (pageIndex < 1)
				pageIndex = 1;
			string sQL = sql.SQL;
			object[] arguments = sql.Arguments;
			this.BuildPagingPrimaryKeyQueries<TEntity>(maxRecords, (pageIndex - 1) * pageSize, pageSize, sQL, ref arguments, out string sqlCount, out string sqlPage);
			return new PagingEntityIdCollection(
				 FetchFirstColumn<object>(sqlPage, arguments),
				 ExecuteScalar<int>(sqlCount, arguments));
		}

#if ASYNC
        /// <summary>
        /// 获取可分页的主键集合 
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="maxRecords"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<PagingEntityIdCollection> FetchPagingPrimaryKeysAsync<TEntity>(long maxRecords, int pageSize, int pageIndex, Sql sql) where TEntity : IEntity
        {
            if (pageIndex < 1)
                pageIndex = 1;
            string sQL = sql.SQL;
            object[] arguments = sql.Arguments;
            this.BuildPagingPrimaryKeyQueries<TEntity>(maxRecords, (pageIndex - 1) * pageSize, pageSize, sQL, ref arguments, out string sqlCount, out string sqlPage);
            return new PagingEntityIdCollection(
                await FetchFirstColumnAsync<object>(sqlPage, arguments),
                await ExecuteScalarAsync<int>(sqlCount, arguments));
        }
#endif

		/// <summary>
		/// 获取可分页的主键集合 
		/// </summary>
		/// <param name="maxRecords"></param>
		/// <param name="pageSize"></param>
		/// <param name="pageIndex"></param>
		/// <param name="primaryKey"></param>
		/// <param name="sql"></param>
		/// <returns></returns>
		public PagingEntityIdCollection FetchPagingPrimaryKeys(long maxRecords, int pageSize, int pageIndex, string primaryKey, Sql sql)
		{
			string sQL = sql.SQL;
			object[] arguments = sql.Arguments;
			this.BuildPagingPrimaryKeyQueries(maxRecords, (pageIndex - 1) * pageSize, pageSize, primaryKey, sQL, ref arguments, out string sqlCount, out string sqlPage);
			return new PagingEntityIdCollection(FetchFirstColumn<object>(sqlPage, arguments), ExecuteScalar<int>(sqlCount, arguments));
		}

#if ASYNC
        /// <summary>
        /// 获取可分页的主键集合 
        /// </summary>
        /// <param name="maxRecords"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="primaryKey"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<PagingEntityIdCollection> FetchPagingPrimaryKeysAsync(long maxRecords, int pageSize, int pageIndex, string primaryKey, Sql sql)
        {
            string sQL = sql.SQL;
            object[] arguments = sql.Arguments;
            this.BuildPagingPrimaryKeyQueries(maxRecords, (pageIndex - 1) * pageSize, pageSize, primaryKey, sQL, ref arguments, out string sqlCount, out string sqlPage);
            return new PagingEntityIdCollection(await FetchFirstColumnAsync<object>(sqlPage, arguments), await ExecuteScalarAsync<int>(sqlCount, arguments));
        }
#endif

		/// <summary>
		/// 获取前topNumber条记录
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="topNumber"></param>
		/// <param name="sql"></param>
		/// <returns></returns>
		public IEnumerable<T> FetchTop<T>(int topNumber, Sql sql)
		{
			object[] arguments = sql.Arguments;
			string topSql = this.BuildTopSql(topNumber, sql.SQL);
			return Fetch<T>(topSql, arguments);
		}

#if ASYNC
        /// <summary>
        /// 获取前topNumber条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="topNumber"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> FetchTopAsync<T>(int topNumber, Sql sql)
        {
            object[] arguments = sql.Arguments;
            string topSql = this.BuildTopSql(topNumber, sql.SQL);
            return await FetchAsync<T>(topSql, arguments);
        }
#endif

		/// <summary>
		/// 获取前topNumber条记录
		/// </summary>
		/// <typeparam name="TEntity"></typeparam>
		/// <param name="topNumber"></param>
		/// <param name="sql"></param>
		/// <returns></returns>
		public IEnumerable<object> FetchTopPrimaryKeys<TEntity>(int topNumber, Sql sql) where TEntity : IEntity
		{
			object[] arguments = sql.Arguments;
			string topSql = this.BuildTopSql<TEntity>(topNumber, sql.SQL);
			return FetchFirstColumn<object>(topSql, arguments);
		}

#if ASYNC
        /// <summary>
        /// 获取前topNumber条记录
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="topNumber"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<IEnumerable<object>> FetchTopPrimaryKeysAsync<TEntity>(int topNumber, Sql sql) where TEntity : IEntity
        {
            object[] arguments = sql.Arguments;
            string topSql = this.BuildTopSql<TEntity>(topNumber, sql.SQL);
            return await FetchFirstColumnAsync<object>(topSql, arguments);
        }
#endif


		protected bool SplitSqlForPagingOptimized(long maxRecords, string sql, string primaryKey, out string sqlCount, out string sqlSelectRemoved, out string sqlOrderBy)
		{
			sqlSelectRemoved = string.Empty;
			sqlCount = string.Empty;
			sqlOrderBy = string.Empty;
			var pagingUtility = (PagingHelper)Provider.PagingUtility;
			Match match = pagingUtility.RegexColumns.Match(sql);
			if (!match.Success)
			{
				return false;
			}
			Group group = match.Groups[1];//匹配到查询字段的
			sqlSelectRemoved = sql.Substring(group.Index);//移除select后的

			var count = pagingUtility.RegexDistinct.IsMatch(sqlSelectRemoved) ? group.ToString().Trim() : "1";//COUNT内字段
			sqlCount = $"{sql.Substring(0, group.Index)} COUNT({count}) {sql.Substring(group.Index + group.Length)}";

			Match orderByMatch = pagingUtility.RegexOrderBy.Match(sqlCount);
			if (orderByMatch.Success)
			{
				Group orderByGroup = orderByMatch.Groups[0];
				sqlOrderBy = orderByGroup.ToString();
				sqlCount = sqlCount.Substring(0, orderByGroup.Index) + sqlCount.Substring(orderByGroup.Index + orderByGroup.Length);
			}
			if (maxRecords > 0L)
			{
				if (this.Provider is MySqlDatabaseProvider || this.Provider is MySqlConnectorDatabaseProvider)
				{
					sqlCount = string.Concat(new object[] { "select count(*) from (", sql, " limit ", maxRecords, " ) as TempCountTable" });
				}
				else
				{
					sqlCount = string.Concat(new object[] { "select count(*) from (", sql.Substring(0, group.Index), " top ", maxRecords, " ", primaryKey, " ", sql.Substring(group.Index + group.Length), " ) as TempCountTable" });
				}
			}
			return true;
		}

		#region Transaction

		void IDbContextTransaction.Begin()
		{
			BeginTransaction();
		}

		void IDbContextTransaction.Complete()
		{
			CompleteTransaction();
		}

		void IDbContextTransaction.Abort()
		{
			AbortTransaction();
		}
		#endregion
	}
}
